<?php

namespace app\ecshop\admin;

use app\common\controller\Common;
use app\yy\model\Website as WebModel;
use think\Db;
use think\Exception;

class Task extends Common
{

    /**
     * 导出
     * @param $id
     * @return void
     * @throws Exception
     */
    public function derive($id='')
    {
        halt($id);
        $post = $this->request->post();
        $post = array_filter($post);
        if(!$id && count($post) < 4){
            $data = [
                '请联系管理员操作',
                '请先准备好,数据库链接信息',
            ];
            halt($data);
        }
        set_time_limit(0);
        ini_set('memory_limit', '4000M');
        if (count($post) == 4){
            $dbstr = [
                'type' => 'mysql',
                'hostname' => $post['db_hostname'],
                'database' => $post['db_database'],
                'username' => $post['db_username'],
                'password' => $post['db_password'],
                'charset' => 'utf8mb4'
            ];
        }else{
            $info = WebModel::where('id', $id)->order('id')->find();
            $dbstr = $this->checkDb($info);
        }

        $opn_array = Db::connect($dbstr)->table('product_option')->column('name','product_option_id');
        $opv_array = Db::connect($dbstr)->table('product_option_value')->column('name','product_option_value_id');
        $head = ["v_sku","v_name","v_short_description","v_description","v_image","v_images","v_url","v_attribute","v_price","v_specials_price","v_specials_expire_date","v_date_added","v_in_stock","v_status","v_viewed","v_ordered","v_category_sku","v_sort_order","v_meta_title","v_meta_keywords","v_meta_description","v_brand_filter","v_class_filter","v_color_filter","v_gender_filter","v_material_filter","v_origin_filter","v_series_filter","v_spec_filter","v_year_filter"];
        $file_name = 'product_old' . date('YmdHis') . '.csv';

        $list = Db::connect($dbstr)->table('product')->alias('p')
            ->join('category c','p.master_category_id = c.category_id','left')
            ->field('p.product_id,p.sku, p.name, p.short_description, p.description, p.image, p.url, p.price, p.specials_price,p.specials_expire_date, p.date_added, p.in_stock, p.status, c.sku category_sku, p.sort_order, p.meta_title,p.meta_keywords, p.meta_description, p.filter_1, p.filter_2, p.filter_3, p.filter_4, p.filter_5,p.filter_6, p.filter_7, p.filter_8, p.filter_9')
            ->order('p.product_id')
            ->select();
        $category = Db::connect($dbstr)->table('category')->field('category_id id, name, parent_id, sort_order, status, top')->select();
        foreach ($category as $cate){
            $this->_data[$cate['parent_id']][] = $cate;
        }
        $availabCategoryList = $this->_build(0);
        $pro_result = Db::connect($dbstr)->table('product_to_category')->field('product_id,group_concat(category_id) AS cate_ids')->group('product_id')->select();

        $pro_to_cate_data = [];

        foreach ($pro_result as $pro){
            if (!empty($pro['cate_ids'])){
                $cate_ids = explode(',',$pro['cate_ids']);
                $cates_str_list = [];
                foreach ($cate_ids as $cid){
                    if (!empty($availabCategoryList[$cid]['name'])){
                        $cates_str_list[] = str_replace(' > ','/',$availabCategoryList[$cid]['name']);
                    }
                }
                $cates_str = implode(',',$cates_str_list);
                $pro_to_cate_data[$pro['product_id']] = $cates_str;
            }
        }

        $attribute = Db::connect($dbstr)->table('product_attribute')->select();
        $images = Db::connect($dbstr)->table('product_to_images')->select();
        $product_images = [];
        foreach ($images as $img){
            $product_images[$img['product_id']][] = $img['image'];
        }
        $product_attribute = [];
        foreach ($attribute as $attr){
            $product_attribute[$attr['product_id']][] = $attr;
        }
        $data = [];
        foreach ($list as $item){
            $p_attr = $product_attribute[$item['product_id']] ?? [];
            $csv_attr_list = [];
            foreach ($p_attr as $p_attr_v){
                if (!isset($csv_attr_list[$p_attr_v['product_option_id']])){
                    $csv_attr_list[$p_attr_v['product_option_id']] = $opn_array[$p_attr_v['product_option_id']].'#';
                }
                $csv_attr_list[$p_attr_v['product_option_id']] = $csv_attr_list[$p_attr_v['product_option_id']].':'.$opv_array[$p_attr_v['product_option_value_id']];
            }
            $csv_attr = implode(';',$csv_attr_list);
            $v_category_sku = !empty($pro_to_cate_data[$item['product_id']])?$pro_to_cate_data[$item['product_id']]:$item['category_sku'];
            $images = $product_images[$item['product_id']] ?? [];
            $temp = [
                "v_sku" => $item['sku'],
                "v_name" => str_replace('"', '""', $item['name']),
                "v_short_description" => str_replace('"', '""', $item['short_description']),
                "v_description" => str_replace('"', '""', $item['description']),
                "v_image" => $item['image'],
                "v_images" => implode(',',$images),
                "v_url" => $item['url'],
                "v_attribute" => $csv_attr,
                "v_price" => $item['price'],
                "v_specials_price" => $item['specials_price'],
                "v_specials_expire_date" => $item['specials_expire_date'],
                "v_date_added" => $item['date_added'],
                "v_in_stock" => $item['in_stock'],
                "v_status" => $item['status'],
                "v_viewed" => '',
                "v_ordered" => '',
                "v_category_sku" => $v_category_sku,
                "v_sort_order" => $item['sort_order'],
                "v_meta_title" => $item['meta_title'],
                "v_meta_keywords" => $item['meta_keywords'],
                "v_meta_description" => str_replace('"', '""', $item['meta_description']),
                "v_brand_filter" => str_replace('"', '""', $item['filter_1']),
                "v_class_filter" => str_replace('"', '""', $item['filter_2']),
                "v_color_filter" => str_replace('"', '""', $item['filter_3']),
                "v_gender_filter" => str_replace('"', '""', $item['filter_4']),
                "v_material_filter" => str_replace('"', '""', $item['filter_5']),
                "v_origin_filter" => str_replace('"', '""', $item['filter_6']),
                "v_series_filter" => str_replace('"', '""', $item['filter_7']),
                "v_spec_filter" => str_replace('"', '""', $item['filter_8']),
                "v_year_filter" => str_replace('"', '""', $item['filter_9'])
            ];
            $data[] = array_values($temp);
        }
        halt($_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['HTTP_HOST'].'/'.$this->xlswriter($file_name,$head,$data));
    }

    public function xlswriter($filename,$head,$data)
    {
        $config = [
            'path' => './uploads/temp' // xlsx文件保存路径
        ];
        $excel  = new \Vtiful\Kernel\Excel($config);
// fileName 会自动创建一个工作表，你可以自定义该工作表名称，工作表名称为可选参数
        $path = $excel->fileName($filename, 'sheet1')
            ->header($head)
            ->data($data)
            ->output();
        return str_replace('./','',$path);
    }

    public $_data = [];
    private function _build($parent_id, $result = array())
    {
        if (isset($this->_data[$parent_id])) {
            foreach ($this->_data[$parent_id] as $category) {
                if ($parent_id == 0) {
                    $result[$category['id']]['name'] = $category['name'];
                } else {
                    $result[$category['id']]['name'] = $result[$parent_id]['name'] . ' > ' . $category['name'];
                }
                $result[$category['id']]['sort_order'] = $category['sort_order'];
                $result[$category['id']]['status'] = $category['status'];
                $result[$category['id']]['top'] = $category['top'];
                if (isset($this->_data[$category['id']])) {
                    $result = $this->_build($category['id'], $result);
                }
            }
        }
        return $result;
    }
}